#coding=utf-8

#操作sqlite3的函式庫，功能在LoNote中比較低階。

import sqlite3, hashlib, zlib, uuid

import lonote.google_dmp.diff_match_patch as diff_match_patch

import lonote.html as html

#初始化全域物件
dmp = diff_match_patch()

currentNotebookVersion = 5 #當前使用的資料庫版本號

def tableinit(dbpath):
    '''把table初始化'''
    conn = sqlite3.connect(dbpath)
    conn.execute('''create table notes
    (id integer not null,
    name text not null,
    parentid integer default null,
    ord integer not null,
    content text default "",
    cretime real not null,
    modtime real not null,
    tree_expanded boolean not null default 1,
    list_expanded boolean not null default 1,
    contentplain text default "",
    primary key(id),
    foreign key(parentid) references notes(id))
    ''')
    conn.execute('''create table history
    (id integer not null,
    name text not null,
    modtime real not null,
    content text,
    foreign key(id) references notes(id))''')
    conn.commit()
    conn.close()

def getVersion(dbpath):
    '''取得db的版本與說明
    回傳：
    dbversion, 說明文字'''
    conn = sqlite3.connect(dbpath)
    dbversion = conn.execute('pragma user_version').fetchone()[0]
    conn.close()
    return dbversion

def checkUpgrade(dbpath):
    '''檢查db的欄位存在或否。如果不存在，添加他。'''
    conn = sqlite3.connect(dbpath)
    try: #notes中的scrollpos
        conn.execute('select scrollpos from notes')
    except sqlite3.OperationalError:
        conn.execute('alter table notes add scrollpos integer not null default 0')
    try: #notes中的bookmark存在或否
        conn.execute('select bookmark from notes')
    except sqlite3.OperationalError:
        conn.execute('alter table notes add bookmark integer default null')
    try: #history中deltafrom的存在或否
        conn.execute('select deltafrom from history')
    except sqlite3.OperationalError:
        conn.execute('alter table history add deltafrom real default null references history(modtime) on delete cascade')
    try: #history中sha224存在或否
        conn.execute('select sha224 from history')
    except sqlite3.OperationalError:
        conn.execute('alter table history add sha224 text default null')

        def genSha224():
            '''替所有history項目產生sha224'''
            for noteContent,deltafrom,modtime in conn.execute('select content,deltafrom,modtime from history').fetchall():
                #如果content是patch形式，先將他恢復成原本格式
                if deltafrom != None:
                    baseContent = conn.execute('select content from history where modtime = ?', (deltafrom,)).fetchone()[0]
                    noteContent, IS_OK = dmp.patch_apply(dmp.patch_fromText(noteContent), baseContent)
                sha224 = hashlib.sha224(noteContent.encode(encoding = 'utf-8')).hexdigest()
                conn.execute('update history set sha224 = ? where modtime = ?',(sha224, modtime))

        genSha224()
    try: #history中len(HTML原始碼長度)的存在或否
        conn.execute('select len from history')
    except sqlite3.OperationalError:
        conn.execute('alter table history add len integer not null default 0')
        def genLen():
            '''計算所有history項目的長度值'''
            for noteContent,deltafrom,modtime in conn.execute('select content,deltafrom,modtime from history').fetchall():
                #如果content是patch形式，先將他恢復成原本格式
                if deltafrom != None:
                    baseContent = conn.execute('select content from history where modtime = ?', (deltafrom,)).fetchone()[0]
                    noteContent, IS_OK = dmp.patch_apply(dmp.patch_fromText(noteContent), baseContent)
                conn.execute('update history set len = ? where modtime = ?',(len(noteContent), modtime))

        genLen()
        conn.execute('pragma user_version = 1') #變更資料庫版本號

    #壓縮歷史記錄
    if conn.execute('pragma user_version').fetchone()[0] <= 1:
        for id,modtime,content in conn.execute('select id,modtime,content from history').fetchall():
            content = zlib.compress(content.encode('utf-8'))
            conn.execute('update history set content = ? where id = ? and modtime = ?', (content, id, modtime))
        conn.execute('pragma user_version = 2') #變更資料庫版本號
        conn.commit()
        conn.execute('vacuum')

    #將history中len變為純文字長度（不含html標籤）
    if conn.execute('pragma user_version').fetchone()[0] == 2:
        def getHistory(conn, id, modtime):
            '''取出指定的歷史記錄content。
            不管底層格式為什麼，自動將其還原回標準的html格式'''
            content,deltafrom = conn.execute('select content,deltafrom from history where id = ? and modtime = ?', (id,modtime)).fetchone()
            #content解壓縮
            content = zlib.decompress(content).decode('utf-8')
            #測試是否需要復原delta
            if deltafrom is None: #如果沒有delta訊息就不用做任何事
                pass
            else: #否則就將他恢復
                #載入delta來源
                deltaFromContent = conn.execute('select content from history where id = ? and modtime = ?', (id,deltafrom)).fetchone()[0]
                #deltaFromContent解壓縮
                deltaFromContent = zlib.decompress(deltaFromContent).decode('utf-8')
                content,is_OK = dmp.patch_apply(dmp.patch_fromText(content), deltaFromContent)
            return content

        #計算所有history項目的長度值(純文字部份)
        for id,modtime in conn.execute('select id,modtime from history').fetchall():
            charcount = len(html.totxt(getHistory(conn, id, modtime)))
            conn.execute('update history set len = ? where modtime = ? and id = ?', (charcount, modtime, id))
        conn.execute('pragma user_version = 3') #變更資料庫版本號
        conn.commit()

    #在notes中新增len
    if conn.execute('pragma user_version').fetchone()[0] == 3:
        #新增len，並計算所有現存contentplain項目的長度值（純文字部份）
        conn.execute('alter table notes add len integer not null default 0')
        for id,contentPlain in conn.execute('select id,contentplain from notes').fetchall():
            charcount = len(contentPlain)
            conn.execute('update notes set len = ? where id = ?', (charcount, id))
        conn.execute('pragma user_version = 4') #變更資料庫版本號
        conn.commit()

    #在notes中新增uuid
    if conn.execute('pragma user_version').fetchone()[0] == 4:
        conn.execute('alter table notes add uuid string default null')
        for item in conn.execute('select id from notes').fetchall():
            conn.execute('update notes set uuid = ? where id = ?', (str(uuid.uuid4()),item[0]))
        conn.execute('pragma user_version = 5') #變更資料庫版本號
        conn.commit()

    conn.commit()
    conn.close()

